﻿using MySql.Data.MySqlClient;
using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;
using System.Xml.Serialization;

namespace RantopadService.Business
{
    public class MessageBusiness
    {
        public static DataTable getAllMessage()
        {
            string mysql = "SELECT * from pu_message ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }

        public static DataTable getMsgByUserID(string name)
        {
            string mysql = "SELECT * from pu_message where (USER_ID=\"" + name + "\"  or USER_ID=\"0\") and PUSHED='0' and datediff(now(),TM)<TERM";

            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getMsgByUserID(string name,string wherelike)
        {
            string mysql = "SELECT * from pu_message where (USER_ID=\"" + name + "\"  or USER_ID=\"0\") and PUSHED='0' and CONTENT like '%"+wherelike+"%' ";

            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static Byte[] getMsgImageByID(string ID)
        {
            string mysql = "SELECT MESSAGE_IMAGE from pu_message where (ID=\"" + ID + "\" ) and PUSHED='0' ";
            //创建数据库连接

            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand comm = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = comm.ExecuteReader();
            byte[] imageByte = null;
            if (dr.Read())
            {
                //读出图片字节数组至byte[] 
                imageByte = new byte[dr.GetBytes(0, 0, null, 0, int.MaxValue)];
                dr.GetBytes(0, 0, imageByte, 0, imageByte.Length);
            }
            myconnection.Close();
            return imageByte;
        }




        public static DataTable getAllMessage(string wherelike, string pushed)
        {
            string mysql = "SELECT * from pu_message  where (CONTENT like '%{0}%' ) and PUSHED like '%{1}%'";
            mysql = string.Format(mysql, wherelike, pushed);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static int getAllMessageCount()
        {
            string mysql = "SELECT count(*) as count from pu_message ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int getAllMessageCount(string wherelike, string online)
        {
            string mysql = "SELECT count(USER_ID) as count from pu_message where (USER_ID like '%{0}%' or content like '%{0}%')  ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }

        public static int delMessageByID(string userName)
        {
            string mysql = "delete from pu_message where ID='{0}'";
            mysql = string.Format(mysql, userName);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();
            myconnection.Close();

            return count;
        }



        public static int addMessage(string content, byte[] msgImage)
        {
            //INSERT INTO `rantopad`.`pu_message` (`USER_ID`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;
            try
            {
                var userDT=UserBusiness.getAllUsers();

                //foreach (DataRow user in userDT.Rows)
                //{
                    string mysql = "insert into `pu_message` (`CONTENT`, `MESSAGE_IMAGE`, `TM`, `USER_ID`) VALUES (@content, @image, @time, @user) ";
                    //创建数据库连接
                    var myconnection = MyMySqlHelper.GetConn();
                    myconnection.Open();
                    MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
                    MySqlParameter prarm1 = new MySqlParameter("@content", MySqlDbType.VarChar);
                    prarm1.Value = content;
                    MySqlParameter param2 = new MySqlParameter("@image", MySqlDbType.MediumBlob);
                    param2.Value = msgImage;
                    MySqlParameter param3 = new MySqlParameter("@time", MySqlDbType.DateTime);
                    param3.Value = DateTime.Now;
                    MySqlParameter param4 = new MySqlParameter("@user", MySqlDbType.String);
                    param4.Value = "0";
                    mycommand.Parameters.Add(prarm1);
                    mycommand.Parameters.Add(param2);
                    mycommand.Parameters.Add(param3);
                    mycommand.Parameters.Add(param4);
                    count = mycommand.ExecuteNonQuery();
                    myconnection.Close();
                    //foreach (DataRow user in userDT.Rows)
                    //{
                    //    string mysql = "insert into `pu_message` (`CONTENT`, `MESSAGE_IMAGE`, `TM`, `USER_ID`) VALUES (@content, @image, @time, @user) ";
                    //    //创建数据库连接
                    //    myconnection = MyMySqlHelper.GetConn();
                    //    myconnection.Open();
                    //     mycommand = new MySqlCommand(mysql, myconnection);
                    //     prarm1 = new MySqlParameter("@content", MySqlDbType.VarChar);
                    //    prarm1.Value = content;
                    //     param2 = new MySqlParameter("@image", MySqlDbType.MediumBlob);
                    //    param2.Value = msgImage;
                    //     param3 = new MySqlParameter("@time", MySqlDbType.DateTime);
                    //    param3.Value = DateTime.Now;
                    //     param4 = new MySqlParameter("@user", MySqlDbType.String);
                    //    param4.Value = "0";
                    //    mycommand.Parameters.Add(prarm1);
                    //    mycommand.Parameters.Add(param2);
                    //    mycommand.Parameters.Add(param3);
                    //    mycommand.Parameters.Add(param4);
                    //    count = mycommand.ExecuteNonQuery();
                    //    myconnection.Close();
                    //}

                
            }
            catch (Exception )
            {
                count = 0;
            }


            return count;
        }
        public static int addMessageToUser(string userID,string content, byte[] msgImage)
        {
            //INSERT INTO `rantopad`.`pu_message` (`USER_ID`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "insert into `pu_message` (`CONTENT`, `MESSAGE_IMAGE`, `TM`, `USER_ID`) VALUES (@content, @image, @time, @user) ";
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
                MySqlParameter prarm1 = new MySqlParameter("@content", MySqlDbType.VarChar);
                prarm1.Value = content;
                MySqlParameter param2 = new MySqlParameter("@image", MySqlDbType.MediumBlob);
                param2.Value = msgImage;
                MySqlParameter param3 = new MySqlParameter("@time", MySqlDbType.DateTime);
                param3.Value = DateTime.Now;
                MySqlParameter param4 = new MySqlParameter("@user", MySqlDbType.String);
                param4.Value = userID;
    
                mycommand.Parameters.Add(prarm1);
                mycommand.Parameters.Add(param2);
                mycommand.Parameters.Add(param3);
                mycommand.Parameters.Add(param4);
                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }

        internal static int updateMessage(string id, string content, byte[] msgImage)
        {
            int count = 0;
            //try
            //{
            //    string mysql = "update `pu_message` set `EMAIL`='{2}', `USER_PSW`='{1}'  where `USER_ID`='{0}'";
            //    mysql = string.Format(mysql, name, psd, email);
            //    //创建数据库连接
            //    var myconnection = MyMySqlHelper.GetConn();
            //    myconnection.Open();
            //    MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            //    count = mycommand.ExecuteNonQuery();
            //}
            //catch (Exception)
            //{
            //    count = 0;
            //}

            return count;
        }

        internal static int updateMessageStatus(string id)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `pu_message` set `PUSHED`='2'  where `ID`='{0}'";
                mysql = string.Format(mysql, id);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }

            return count;
        }
    }
}